﻿//Copyright (C) <2013>  <jonathan cleeve norton> All Rights Reserved 
//Contact jon.norton@fin-plus.co.uk website <http://www.fin-plus.co.uk/>
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlServerCe;
using System.Data.SqlClient;
using Messaging2;
using FinPlusCommon;

namespace FinPlusDBConn
{
    public enum QueryType { Select, SelectAsOf, SelectFrom, SaveFunc, CloseFunc, GetFuncId, LastChanged };
    public enum ConType { None, All, NewAndAmend, New, Amend };

    public sealed class Query : IDisposable, IFinPlusQuery
    {
        public IFinPlusFunctions Functions  { get; private set; }
        public ConType ConType { get; private set; }
        public string QueryString { get; private set; }
        public string Group { get; private set; }

        private readonly SqlCeConnection _conn;
        private readonly QueryType _queryType;
        private readonly DateTime _asOf, _from;
        private ITransport _tran;
        private readonly string _table, _tableId, _where, _user;
        private const string _dbDateFormat = "yyyy-MM-dd HH:mm:ss";

        public Query(SqlCeConnection conn, ITransport tran, QueryType queryType, string group, DateTime asOf, DateTime from, string where, ConType conType, string displayFields, string displayOrder)
        {
            Functions = new Functions();
            if(!string.IsNullOrEmpty(displayFields)) Functions.DisplayFields = displayFields.Split(',');
            if(!string.IsNullOrEmpty(displayOrder)) Functions.DisplayOrder = displayOrder.Split(',');
            _queryType = queryType;
            _conn = conn;
            _tran = tran;
            _table = group + "Items";
            _tableId = group + "Ids";
            _asOf = asOf;
            _from = from;
            _where = where;
            Group = group;
            ConType = conType;
            QueryString = QString(_queryType);
        }

        public Query(IFinPlusFunction func, SqlCeConnection conn, ITransport tran, QueryType queryType, string group, string user)
        {
            Functions = new Functions();
            Functions.Add(func, false, false);
            _conn = conn;
            _tran = tran;
            _queryType = queryType;
            Group = group;
            _table = group + "Items";
            _tableId = group + "Ids";
            _user = user;

            QueryString = QString(_queryType);
        }

        public bool Update(IFinPlusFunction func)
        {
            if (!func.IsMatch(_where))
            {
                if (Functions.ContainsKey(func.Id)) 
                    Functions.Update(func, ChangeType.Removed);
                return true;
            }

            if(_queryType == QueryType.Select || _queryType == QueryType.SelectFrom)
            {
                Functions.Update(func, func.IsClosed ?  ChangeType.Removed : (Functions.ContainsKey(func.Id) ? ChangeType.Altered : ChangeType.New));
                return true; 
            }

            return false; 
        }

        public void Execute()
        {
            switch (_queryType)//executes go through one piont
            {
              
                case QueryType.SelectAsOf:  
                    ExecuteSelect(QueryString); break;
                case QueryType.Select:
                case QueryType.SelectFrom:  
                    if (ConType != ConType.None) 
                        _tran.Listen(Group, false); 
                        ExecuteSelect(QueryString); break;
                case QueryType.SaveFunc: ExecuteSave(QueryString); break;
                case QueryType.CloseFunc: ExecuteClose(QueryString); break;
            }
        }

        public void Dispose()
        {
            _tran.Listener.Stop(Group);
            //do not dispose of tran and conn owned by other classes
        }

        //private
        private void ExecuteSelect(string queryString)
        {
            try
            {
                //get items turn into funcs
                using (var cmd = new SqlCeCommand(queryString, _conn))
                {
                    using (SqlCeDataReader r = cmd.ExecuteReader())
                    {
                        while (r.Read())
                        {
                            var paramPos = int.Parse(r["ParamPos"].ToString());
                            var id = int.Parse( r["Id"].ToString());
                            var timeStamp = DateTime.Parse(r["TimeStamp"].ToString());

                            var item = new Item(paramPos, id, r["Name"].ToString(), r["Value"].ToString(), r["Type"].ToString(), timeStamp, r["User"].ToString());
                            var func = Functions.ContainsKey(item.Id) ? Functions[item.Id] : Functions.Add(new Function() { Id = item.Id }, false, false);

                            if (item.Name.Equals("IsClosed")) func.IsClosed =  (bool)item.Object;
                            
                            func.TryAdd(item);
                        }
                    }
                }

                if(!string.IsNullOrEmpty(_where))
                    Functions.Remove(_where.Replace("'", "\"")); //where can macth on hitorical data some cases might need some might not put this in for now
            }
            catch 
            {
                //throw new QueryExecuteException(QueryString, e);
            }
        }

        private void ExecuteSave(string queryString, bool withPublish = true)
        {
            var timeStamp = DateTime.Now;

            foreach (var func in Functions.Values)//save each function
            {
                try 
                {
                    if (func.Id < 1) 
                        GetFuncId(QString(QueryType.GetFuncId), func);
                    
                    var publish = false;

                    using (var cmdSave = new SqlCeCommand(queryString, _conn))
                    {
                        cmdSave.Parameters.Add("@ParamPos", SqlDbType.Int);
                        cmdSave.Parameters.Add("@Id", SqlDbType.Int);
                        cmdSave.Parameters.Add("@Name", SqlDbType.NVarChar, 50);
                        cmdSave.Parameters.Add("@Value", SqlDbType.NVarChar, 200);
                        cmdSave.Parameters.Add("@Type", SqlDbType.NVarChar, 50);
                        cmdSave.Parameters.Add("@TimeStamp", SqlDbType.DateTime);
                        cmdSave.Parameters.Add("@User", SqlDbType.NVarChar, 50);

                        foreach (var item in func)
                        {
                            if (item.TimeStamp == DateTime.MinValue || item.Id < 0)
                            {
                                item.Id = func.Id;
                                item.TimeStamp = timeStamp;
                                publish = true;
                                if (item.Name == "Id") { item.Set(func.Id.ToString(), null, false); }
                                if (item.Name == "TimeStamp") { item.Set(timeStamp.ToOADate().ToString(), null, false); }
                                    
                                cmdSave.Parameters["@ParamPos"].Value = item.Index;
                                cmdSave.Parameters["@Id"].Value = item.Id;
                                cmdSave.Parameters["@Name"].Value = item.Name;
                                cmdSave.Parameters["@Value"].Value = item.Value;
                                cmdSave.Parameters["@Type"].Value = item.Type;
                                cmdSave.Parameters["@TimeStamp"].Value = timeStamp;
                                cmdSave.Parameters["@User"].Value = item.User;
                                cmdSave.ExecuteNonQuery();
                            }
                        }
                    }

                    ExecuteLastChanged(QString(QueryType.LastChanged), func.Id, timeStamp);

                    if (publish && withPublish) 
                        _tran.Publish(new Message(func.ToArray(), Group, ActType.New));//for real-time, you could just publish individual dirty items for better deserialisatin
                }
                catch (Exception e)
                {
                    throw new QueryExecuteException(QueryString, e);
                }
            }
        }

        private void ExecuteLastChanged(string queryString, int id, DateTime timeStamp)
        {
            using (var cmdChange = new SqlCeCommand(queryString, _conn))
            {
                cmdChange.Parameters.Add("@LastChanged", SqlDbType.DateTime);
                cmdChange.Parameters.Add("@Id", SqlDbType.Int);
                cmdChange.Parameters["@LastChanged"].Value = timeStamp;
                cmdChange.Parameters["@Id"].Value = id;
                cmdChange.ExecuteNonQuery();
            }
        }

        private void ExecuteClose(string queryString)
        {
            foreach (var func in Functions.Values)//close each function
            {
                try
                {
                    using (var cmdClose = new SqlCeCommand(queryString + func.Id, _conn))
                        cmdClose.ExecuteNonQuery();

                    func.Add("IsClosed", func.IsClosed = true, _user);
                    ExecuteSave(QString(QueryType.SaveFunc),  false);

                    _tran.Publish(new Message(func.ToArray(), Group, ActType.Close));//for real-time, you could just publish individual dirty items for better deserialisatin
                }
                catch (Exception e)
                {
                    throw new QueryExecuteException(QueryString, e);
                }
            }
        }

        private void GetFuncId(string queryString, IFinPlusFunction func)
        {
            try
            {
                using (var cmd = new SqlCeCommand(queryString, _conn))
                {
                    cmd.Parameters.Add("@TimeStamp", SqlDbType.DateTime).Value = DateTime.Now;
                    cmd.Parameters.Add("@LastChanged", SqlDbType.DateTime).Value = DateTime.Now;
                    cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 50).Value = _user;
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "SELECT @@IDENTITY";
                    //note CE does not support SCOPE_IDENTITY
                    func.Id = int.Parse(cmd.ExecuteScalar().ToString());
                }
            }
            catch(Exception e)
            {
                throw new QueryExecuteException("Could not get function id", e);
            }
        }

        private string QString(QueryType queryType)
        {
            //action query strings
            if (queryType == QueryType.LastChanged) return string.Format("UPDATE [{0}] SET LastChanged = @LastChanged WHERE [Id] = @Id", _tableId);

            if (queryType == QueryType.GetFuncId) return string.Format(@"INSERT INTO [{0}] ([TimeStamp], [LastChanged], [User]) VALUES (@TimeStamp, @LastChanged, @Name)", _tableId);

            if (queryType == QueryType.SaveFunc) return string.Format(@"INSERT INTO [{0}] ([ParamPos], [Id] ,[Name], [Value], [Type], [TimeStamp], [User]) VALUES (@ParamPos, @Id, @Name, @Value, @Type, @TimeStamp, @User)", _table);

            if (queryType == QueryType.CloseFunc) return string.Format(@"UPDATE [{0}] SET Closed = '{1}', LastChanged = '{1}', ClosedBy = '{2}'  WHERE [Id] = ", _tableId, DateTime.Now.ToString(_dbDateFormat), _user);

            //select query strings
            var s = new StringBuilder().Append(string.Format(@"SELECT * FROM {0} WHERE", _table));
            bool needsAnd = false;

            if (_where != string.Empty) //where clause on any item in function 
            {
                s.Append(string.Format(@" Id IN(SELECT Id FROM {0} WHERE {1})", _table, _where));
                needsAnd = true;
            }
            //AsOf
            if (_asOf != DateTime.MinValue && ConType == ConType.Amend) //created before a set date and not closed before set date
            {
                if (needsAnd) s.Append(@" AND");
                s.Append(string.Format(@" Id IN(SELECT Id FROM {0} WHERE TimeStamp < '{1}' AND  LastChanged > '{1}' AND (Closed > '{1}' OR Closed IS NULL)) AND TimeStamp < '{1}'", _tableId, _asOf.ToString(_dbDateFormat)));
                needsAnd = true;
            }
            else if (_asOf != DateTime.MinValue) //created before a set date and not closed before set date
            {
                if (needsAnd) s.Append(@" AND");
                s.Append(string.Format(@" Id IN(SELECT Id FROM {0} WHERE TimeStamp < '{1}' AND (Closed > '{1}' OR Closed IS NULL)) AND TimeStamp < '{1}'", _tableId, _asOf.ToString(_dbDateFormat)));
                needsAnd = true;
            }
            //From
            else if (_from != DateTime.MinValue && ConType == ConType.All) //created from a set date amends, new and closes.
            {
                if (needsAnd) s.Append(@" AND");
                s.Append(string.Format(@" Id IN(SELECT Id FROM {0} WHERE LastChanged > '{1}')", _tableId, _from.ToString(_dbDateFormat)));
            }
            else if (_from != DateTime.MinValue && ConType == ConType.NewAndAmend) //created from a set new and amends only.
            {
                if (needsAnd) s.Append(@" AND");
                s.Append(string.Format(@" Id IN(SELECT Id FROM {0} WHERE LastChanged > '{1}' AND Closed IS NULL )", _tableId, _from.ToString(_dbDateFormat)));
            }
            else if (_from != DateTime.MinValue && ConType == ConType.New) //created from a set new only.
            {
                if (needsAnd) s.Append(@" AND");
                s.Append(string.Format(@" Id IN(SELECT Id FROM {0} WHERE TimeStamp > '{1}' AND Closed IS NULL )", _tableId, _from.ToString(_dbDateFormat)));
            }
            else if (_from != DateTime.MinValue && ConType == ConType.Amend) //created from a set date amends only.
            {
                if (needsAnd) s.Append(@" AND");
                s.Append(string.Format(@" Id IN(SELECT Id FROM {0} WHERE TimeStamp < '{1}' AND LastChanged > '{1}' AND Closed IS NULL )", _tableId, _from.ToString(_dbDateFormat)));
            }
            //select
            else
            {
                if (needsAnd) s.Append(@" AND");
                s.Append(string.Format(@" Id IN(SELECT Id FROM {0} WHERE Closed IS NULL )", _tableId));
            }

            return s.ToString();
        }

        public class QueryExecuteException : Exception
        {
            public QueryExecuteException(string message, Exception innerException) : base(message, innerException) { }
            public QueryExecuteException(string message) : this(message, null) { }
        }
    }
}
